package com.sl.au.service.impl;


import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;
import javax.persistence.Table;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.StringUtils;

import com.sl.au.repository.DivisionRepository;
import com.sl.au.repository.HierarchyRepository;
import com.sl.au.repository.PartRepository;
import com.sl.au.service.DeleteService;
import com.sl.utils.ExdeUtil;

@Service("DeleteService")
@Transactional
public class DeleteServiceImpl extends BaseServiceImpl implements DeleteService{
	
	@Resource
	private JdbcTemplate jdbcTemplate;
	
	@Autowired
	private HierarchyRepository hierarchyRepository;
	
	@Autowired
	private DivisionRepository divisionRepository;
	
	@Autowired
	private PartRepository partRepository;
	
	@Resource
	private NamedParameterJdbcTemplate namedParameterJdbcTemplate;
	
	@Override
	public Map<String, Object> deleteInfo(String datasourceName, Class clazz ,String id) {
		Map<String, Object> result = new HashMap<String, Object>();
		int code = 0;
		String msg = "";
		String tableName = "";
		Table annotation = (Table)clazz.getAnnotation(Table.class);
        if(annotation == null){
        		code = 99;
        		msg = "未找到查询实体所对应表名!";
        }else {
	        tableName = annotation.name();
	        
			
			List<Map<String,Object>> lst = new ArrayList<Map<String,Object>>();
			String sql = "SELECT TABLE_NAME AS `tableName`, COLUMN_NAME AS `colName` FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE REFERENCED_TABLE_NAME = '"+tableName+"' AND TABLE_SCHEMA = '"+datasourceName+"' ORDER BY TABLE_NAME DESC ";
			lst = jdbcTemplate.queryForList(sql);
			if(lst.size() > 0) {
				for(int i=0;i<lst.size();i++) {
					Map<String, Object> map = lst.get(i);
					String delSQL = "select count(1) from " + map.get("tableName") + " where " + map.get("colName") + "='" + id +"'";
					int count = jdbcTemplate.queryForObject(delSQL, Integer.class);
					if(count > 0) {
						List<Map<String,Object>> tabConfigs = new ArrayList<Map<String,Object>>();
						String tabSql = "SELECT tableDescribe from sl_wcsi_tableConfig where tableName = '"+map.get("tableName")+"' and `isUse` = '0' ";
						tabConfigs = jdbcTemplate.queryForList(tabSql);
						if(tabConfigs.size() > 0) {
							String tableDescribe = String.valueOf(tabConfigs.get(0).get("tableDescribe"));
							code = 1;
							msg = tableDescribe + "存在数据，不能进行删除";
							break;
						}
						
					}
				}
			}
        }
        
        result.put("code", code);
		result.put("msg", msg);
		return result;
	}
	
	
	

	@Override
	public boolean deleteInfoAllForClass(String datasourceName, Class clazz, String id) {
		String tableName = "";
		boolean result = false;
		Table annotation = (Table)clazz.getAnnotation(Table.class);
        if(annotation == null){
        		result = false;
        }else {
        		result = true;
	        tableName = annotation.name();
			List<Map<String,Object>> lst = new ArrayList<Map<String,Object>>();
			String sql = "SELECT TABLE_NAME AS `tableName`, COLUMN_NAME AS `colName` FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE REFERENCED_TABLE_NAME = '"+tableName+"' AND TABLE_SCHEMA = '"+datasourceName+"' ORDER BY TABLE_NAME DESC ";
			lst = jdbcTemplate.queryForList(sql);
			if(lst.size() > 0) {
				for(int i=0;i<lst.size();i++) {
					Map<String, Object> map = lst.get(i);
					String sTableName = (String) map.get("tableName");
					String sColName = (String) map.get("colName");
					if(sTableName.equals("sl_wcsi_user_grid")) {
						String deleteSql = "delete from "+sTableName + " where "+sColName+" = '"+id+"'";
						System.out.println("删除语句==="+deleteSql);
						int temp = jdbcTemplate.update(deleteSql);
						if (temp > 0) {
				            System.out.println("删除成功");  
				        }else {
				            System.out.println("删除失败");  
				        }
						continue;
					}
					String delSQL = "select id from " + sTableName + " where " + sColName + "='" + id +"'";
					List<Map<String,Object>> delLst = new ArrayList<Map<String,Object>>();
					delLst = jdbcTemplate.queryForList(delSQL);
					if(delLst.size() > 0) {
						for(int j=0;j<delLst.size();j++) {
							Map<String, Object> delMap = delLst.get(j);
							String sId = (String) delMap.get("id");
							boolean delResult = false;
							delResult = deleteInfoForString(datasourceName, sTableName,sId);
							if(!delResult) {
								return delResult;
							}else {
								String deleteSql = "delete from "+sTableName + " where id = '"+sId+"'";
								System.out.println("删除语句==="+deleteSql);
								int temp = jdbcTemplate.update(deleteSql);
								if (temp > 0) {
						            System.out.println("1删除成功");  
						        }else {
						            System.out.println("1删除失败"); 
						        }
							}
						}
					}
				}
			}
			if(result) {
				String deleteSql = "delete from "+tableName + " where id = '"+id+"'";
				System.out.println("删除语句==="+deleteSql);
				int temp = jdbcTemplate.update(deleteSql);
				if (temp > 0) {
					result = true;
		            System.out.println("2删除成功");  
		        }else {
		        		result = false;
		            System.out.println("2删除失败");  
		        }
			}
        }
		return result;
	}
	
	public boolean deleteInfoForString(String datasourceName, String tableName, String id) {
		boolean result = true;
		List<Map<String,Object>> lst = new ArrayList<Map<String,Object>>();
		String sql = "SELECT TABLE_NAME AS `tableName`, COLUMN_NAME AS `colName` FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE REFERENCED_TABLE_NAME = '"+tableName+"' AND TABLE_SCHEMA = '"+datasourceName+"' ORDER BY TABLE_NAME DESC ";
		lst = jdbcTemplate.queryForList(sql);
		if(lst.size() > 0) {
			for(int i=0;i<lst.size();i++) {
				Map<String, Object> map = lst.get(i);
				String sTableName = (String) map.get("tableName");
				String sColName = (String) map.get("colName");
				if(sTableName.equals("sl_wcsi_user_grid")) {
					String deleteSql = "delete from "+sTableName + " where "+sColName+" = '"+id+"'";
					System.out.println("删除语句==="+deleteSql);
					int temp = jdbcTemplate.update(deleteSql);
					if (temp > 0) {
			            System.out.println("删除成功");  
			        }else {
			            System.out.println("删除失败");
			        }
					continue;
				}
				String delSQL = "select id from " + sTableName + " where " + sColName + "='" + id +"'";
				List<Map<String,Object>> delLst = new ArrayList<Map<String,Object>>();
				delLst = jdbcTemplate.queryForList(delSQL);
				if(delLst.size() > 0) {
					for(int j=0;j<delLst.size();j++) {
						Map<String, Object> delMap = delLst.get(j);
						String sId = (String) delMap.get("id");
						boolean delResult = false;
						delResult = deleteInfoForString(datasourceName, sTableName, sId);
						if(!delResult) {
							return delResult;
						}else {
							String deleteSql = "delete from "+sTableName + " where id = '"+sId+"'";
							System.out.println("删除语句==="+deleteSql);
							int temp = jdbcTemplate.update(deleteSql);
							if (temp > 0) {
					            System.out.println("11删除成功");  
					        }else {
					            System.out.println("11删除失败"); 
					        }
						}
					}
					
				}
			}
		}
		if(result) {
			String deleteSql = "delete from "+tableName + " where id = '"+id+"'";
			System.out.println("删除语句==="+deleteSql);
			int temp = jdbcTemplate.update(deleteSql);
			if (temp > 0) {
				result = true;
	            System.out.println("22删除成功");  
	        }else {
	        		result = false;
	            System.out.println("22删除失败");  
	        }
		}
		return result;
	}




	@Override
	public Map<String, Object> deleteListAllForClass(String datasourceName, Class clazz, Date begin, Date end) {
		Map<String, Object> result = new HashMap<String, Object>();
		Table annotation = (Table)clazz.getAnnotation(Table.class);
        if(annotation == null){
        		result.put("msg", "未找到所对应的表！");
        }else {
	        String tableName = annotation.name();
			Map<String, Object> params = new HashMap<String, Object>();
			params.put("begin", begin);
			params.put("end", end);
			int allCount = 0;
			int delCount = 0;
			String SQL = "select id from "+tableName+" where createTime >:begin and createTime <:end ";
			List<Map<String,Object>> lst = new ArrayList<Map<String,Object>>();
			lst = namedParameterJdbcTemplate.queryForList(SQL,params);
			allCount = lst.size();
			if(lst.size() > 0) {
				for(int i=0;i<lst.size();i++) {
					Map<String, Object> map = lst.get(i);
					String id = (String) map.get("id");
					boolean delResult = this.deleteInfoAllForClass(datasourceName, clazz, id);
					if(delResult) {
						delCount ++;
					}
				}
			}
			result.put("msg", "总共 "+ allCount + " 条数据;删除成功 "+delCount+" 条;删除失败 "+ (allCount-delCount) +" 条;");
        }
		
		return result;
	}
}
